1 Imports System.Data.OleDb
2
3 Public Class frmMenuItemsExportImport
4 Public Sub Getdata()
5 Try
6 con = New OleDbConnection(cs)
7 con.Open()
8 cmd = New OleDbCommand("SELECT ItemID,RTRIM(DishName), RTRIM(Category),RTRIM(Kitchen),InventoryType, Rate,Discount from Dish order by DishName", con)
9 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
10 dgw.Rows.Clear()
11 While (rdr.Read() = True)
12 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
13 End While
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
21 GetData()
22 End Sub
23 Sub Reset()
24 txtSearchByDish.Text = ""
25 txtCategory.Text = ""
26 DataGridView1.DataSource = Nothing
27 DataGridView1.Visible = False
28 btnUpdate.Enabled = False
29 GetData()
30 End Sub
31 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
32 Reset()
33 End Sub
34
35 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
36 ExportExcel(dgw)
37 End Sub
38
39 Private Sub txtSearchByDish_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByDish.TextChanged
40 Try
41 con = New OleDbConnection(cs)
42 con.Open()
43 cmd = New OleDbCommand("SELECT ItemID,RTRIM(DishName), RTRIM(Category),RTRIM(Kitchen),InventoryType, Rate,Discount from Dish where DishName like '%" & txtSearchByDish.Text & "%' order by DishName", con)
44 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
45 dgw.Rows.Clear()
46 While (rdr.Read() = True)
47 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
48 End While
49 con.Close()
50 Catch ex As Exception
51 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
52 End Try
53 End Sub
54
55 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
56 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
57 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
58 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
59 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
60 End If
61 Dim b As Brush = SystemBrushes.ControlText
62 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
63 End Sub
64
65 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles btnImportExcel.Click
66 Try
67 Dim OpenFileDialog As New OpenFileDialog
68 OpenFileDialog.Filter = "Excel Files | *.xlsx; *.xls;| All Files (*.*)| *.*"
69 If OpenFileDialog.ShowDialog() = Windows.Forms.DialogResult.OK AndAlso OpenFileDialog.FileName <> "" Then
70 Cursor = Cursors.WaitCursor
71 Timer1.Enabled = True
72 Dim Pathname As String = OpenFileDialog.FileName
73 Dim MyConnection As System.Data.OleDb.OleDbConnection
74 Dim DtSet As System.Data.DataSet
75 Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
76 MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Pathname + ";Extended Properties=Excel 8.0;")
77 MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
78 MyConnection.Open()
79 DtSet = New System.Data.DataSet
80 MyCommand.Fill(DtSet)
81 DataGridView1.Visible = True
82 DataGridView1.DataSource = DtSet.Tables(0)
83 btnUpdate.Enabled = True
84 End If
85 Catch ex As Exception
86 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
87 End Try
88 End Sub
89
90 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
91 Cursor = Cursors.Default
92 Timer1.Enabled = False
93 End Sub
94
95 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
96 Try
97 If DataGridView1.RowCount = Nothing Then
98 MessageBox.Show("Sorry nothing to update.." & vbCrLf & "Please retrieve data in datagridview", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
99 Exit Sub
100 End If
101 For Each row As DataGridViewRow In DataGridView1.Rows
102 Cursor = Cursors.WaitCursor
103 Timer1.Enabled = True
104 con = New OleDbConnection(cs)
105 con.Open()
106 Dim cb As String = "update Dish set DishName=@d1, Category=@d2,Rate=" & Val(row.Cells(5).Value) & ",Discount=" & Val(row.Cells(6).Value) & ",Kitchen=@d3,InventoryType=@d4 where ItemID=@d5"
107 cmd = New OleDbCommand(cb)
108 cmd.Connection = con
109 cmd.Parameters.AddWithValue("@d1", row.Cells(1).Value)
110 cmd.Parameters.AddWithValue("@d2", row.Cells(2).Value)
111 cmd.Parameters.AddWithValue("@d3", row.Cells(3).Value)
112 cmd.Parameters.AddWithValue("@d4", row.Cells(4).Value)
113 cmd.Parameters.AddWithValue("@d5", Val(row.Cells(0).Value))
114 cmd.ExecuteReader()
115 con.Close()
116 Next
117 MessageBox.Show("Successfully Updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
118 DataGridView1.DataSource = Nothing
119 Reset()
120 Catch ex As OleDbException
121 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
122
123 con.Close()
124 End Try
125
126 End Sub
127
128 Private Sub frmMenuItemsExportImport_FormClosing(sender As System.Object, e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
129 frmItem.Getdata()
130 End Sub
131
132 Private Sub txtCategory_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCategory.TextChanged
133 Try
134 con = New OleDbConnection(cs)
135 con.Open()
136 cmd = New OleDbCommand("SELECT ItemID,RTRIM(DishName), RTRIM(Category),RTRIM(Kitchen),InventoryType, Rate,Discount from Dish where Category like '%" & txtCategory.Text & "%' order by DishName", con)
137 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
138 dgw.Rows.Clear()
139 While (rdr.Read() = True)
140 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
141 End While
142 con.Close()
143 Catch ex As Exception
144 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
145 End Try
146 End Sub
147 End Class
